'''
    获取逐笔数据，过滤规则：
    (exchange=5 or exchange=6) and flag=0 and (Conditions!=24 and Conditions!=19 and Conditions!=20) and volume>=100
'''

import pymysql
from DBUtils.PooledDB import PooledDB
from module.common import Config

import logging
log = logging.getLogger("handlelog")

class MysqlHelper(object):
    def __init__(self):
        #sql数据过滤规则
        #self.dataSqlRule = "AND (exchange=5 OR exchange=6) AND flag=0 AND (Conditions!=24 AND Conditions!=19 AND Conditions!=20) AND volume>=100"
        self.dataSqlRule = "and (exchange=6 or exchange=10 or exchange=13) and volume>=100 and flag=0"
        #分页查询
        self.pageSize = 1000

    def loadTickerData(self, skcode, sd, ed):
        tickerdb = pymysql.connect(host=Config.sql_host, user=Config.sql_user,
                                   password=Config.sql_pass, db='ticker', port=int(Config.sql_port))
        cur = tickerdb.cursor()
        tickerData = []
        pageNum = 0
        tableName = "tbl_ticker_us_%s" % skcode.lower().split(".")[1]
        while True:
            pageNum += 1
            whereClause = f"WHERE time >= UNIX_TIMESTAMP('{sd}') AND time <= UNIX_TIMESTAMP('{ed}') {self.dataSqlRule}"
            pageLimit = f"LIMIT {(pageNum-1)*self.pageSize},{self.pageSize}"
            tickerPageSql = f"SELECT time,price,volume FROM {tableName} {whereClause} ORDER BY time {pageLimit}"
            log.debug("%s sql分页查询语句: %s" % (pageNum, tickerPageSql))

            cur.execute(tickerPageSql)
            tickerPageData = cur.fetchall()
            if not tickerPageData:
                break
            tickerData.extend(list(tickerPageData))
            if len(tickerPageData) < self.pageSize:
                break

        tickerdb.close()
        return tickerData


    def queryStockInfo(self, code, datadate, starttime):
        # 获取昨日收盘价
        moneydb = pymysql.connect(host=Config.sql_host, user=Config.sql_user,
                                  password=Config.sql_pass, db='money', port=int(Config.sql_port))
        moneycur = moneydb.cursor()
        whereClause = f"WHERE code='{code}' AND stat_day=UNIX_TIMESTAMP('{datadate} 00:00:00') "
        querySql = f"SELECT DATE(FROM_UNIXTIME(stat_day)),last_close FROM tbl_day_kline {whereClause}"
        moneycur.execute(querySql)
        precPriceInfo = moneycur.fetchone()
        log.info("查询股票补充信息 - 昨日收盘价:%s" % str(precPriceInfo))
        moneydb.close()
        prev_close_price = 0
        if precPriceInfo and precPriceInfo[1]:
            prev_close_price = precPriceInfo[1]

        # 获取最高价/最低价/成交量/成交额 (不管是什么时间范围都要先从盘前第一条逐笔累计)
        tickerdb = pymysql.connect(host=Config.sql_host, user=Config.sql_user,
                                   password=Config.sql_pass, db='ticker', port=int(Config.sql_port))
        tickercur = tickerdb.cursor()
        tableName = "tbl_ticker_us_%s" % code.lower().split(".")[1]
        whereClause = f"WHERE time >= UNIX_TIMESTAMP('{datadate} 00:00:00') AND time <= UNIX_TIMESTAMP('{datadate} {starttime}') {self.dataSqlRule}"
        querySql = f"SELECT MAX(price),MIN(price),SUM(volume),SUM(volume*price) FROM {tableName} {whereClause}"
        tickercur.execute(querySql)
        tickerInfo = tickercur.fetchone()
        log.info("查询股票补充信息 - 推送前最高价/最低价/成交量/成交额:%s" % str(tickerInfo))
        tickerdb.close()
        high_price = tickerInfo[0] if tickerInfo[0] else 0
        low_price = tickerInfo[1] if tickerInfo[1] else 0
        volume = int(tickerInfo[2]) if tickerInfo[2] else 0
        turnover = tickerInfo[3] if tickerInfo[3] else 0

        return prev_close_price,high_price,low_price,volume,turnover


print('测试')
if __name__ == '__main__':
    # skcode = "US.AAPL"
    # startdatetime = "2021-08-19 09:30:00"
    # enddatetime = "2021-08-19 16:00:00"
    #
    # demo = MysqlHelper()
    # tickerData = demo.loadTickerData(skcode, startdatetime, enddatetime)
    # log.info("获取到逐笔数据: %s" % len(tickerData))

    skcode = "US.AAPL"
    datetime = "2021-08-19"
    starttime = "09:30:00"
    demo = MysqlHelper()
    demo.queryStockInfo(skcode, datetime, starttime)